﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
namespace NWBA_Public

{
    public class Bill
    {
        public int ID { get; set; }
        public int AccountNumber { get; set; }
        public int PayeeID { get; set; }
        public decimal Amount { get; set; }
        public DateTime ScheduleDate { get; set; }
        public char Period { get; set; }
        public DateTime ModifyDate { get; set; }
        public Bill(int acctNum, int payeeID, decimal amt, DateTime scheduleDate,
            char period)
        {
            this.AccountNumber = acctNum;
            this.PayeeID = payeeID;
            this.Amount = amt;
            this.ScheduleDate = scheduleDate;
            this.Period = period;
            this.ModifyDate = DateTime.Now;
        }
        public Bill(int id, int acctNum, int payeeID, decimal amt, DateTime scheduleDate,
            char period, DateTime date)
        {
            this.ID = id;
            this.AccountNumber = acctNum;
            this.PayeeID = payeeID;
            this.Amount = amt;
            this.ScheduleDate = scheduleDate;
            this.Period = period;
            this.ModifyDate = date;
        }
    }
    public class BillDisplay
    {
        public int ID { get; set; }
        public string PayeeName { get; set; }
        public decimal Amount { get; set; }
        public DateTime ScheduleDate { get; set; }
        public char Period { get; set; }
        public DateTime ModifyDate { get; set; }
        public BillDisplay(int id, string payeeName, decimal amt, DateTime scheduleDate,
            char period, DateTime date)
        {
            this.ID = id;
            this.PayeeName = payeeName;
            this.Amount = amt;
            this.ScheduleDate = scheduleDate;
            this.Period = period;
            this.ModifyDate = date;
        }
    }
    public class BillRepository
    {
        public static SqlConnection con = new SqlConnection(ServerString.getServerString());
        public BillRepository()
        {
        }
        public static List<Bill> Select(Account acct)
        {
            return Select(acct.AccountNumber);
        }
        public static List<Bill> Select(int accountID)
        {
            List<Bill> _Bills = new List<Bill>();
            con.Open();
            SqlCommand command = new SqlCommand("SELECT BillPayID, AccountNumber, PayeeID, Amount, ScheduleDate, Period, ModifyDate" +
                " FROM [BillPay] WHERE AccountNumber=" + accountID, con);
            SqlDataReader sqlr = command.ExecuteReader();
            while (sqlr.Read())
            {
                _Bills.Add(new Bill(sqlr.GetInt32(0), sqlr.GetInt32(1), sqlr.GetInt32(2),
                    sqlr.GetDecimal(3), sqlr.GetDateTime(4),
                    sqlr.GetString(5).ToCharArray()[0], sqlr.GetDateTime(6)));
            }
            con.Close();
            return _Bills;
        }

        public static Bill SelectSingleBill(int id)
        {
            Bill bill = null;
            con.Open();
            SqlCommand command = new SqlCommand("SELECT AccountNumber, PayeeID, Amount, ScheduleDate, Period, ModifyDate" +
                " FROM [BillPay] WHERE BillPayID=" + id, con);
            SqlDataReader sqlr = command.ExecuteReader();
            while (sqlr.Read())
            {
                bill = new Bill(id, sqlr.GetInt32(0), sqlr.GetInt32(1),
                    sqlr.GetDecimal(2), sqlr.GetDateTime(3),
                    sqlr.GetString(4).ToCharArray()[0], sqlr.GetDateTime(5));
                break;
            }
            con.Close();
            return bill;
        }

        public static List<BillDisplay> DisplayBills(int accountID)
        {
            List<BillDisplay> _Bills = new List<BillDisplay>();
            con.Open();
            SqlCommand command = new SqlCommand("SELECT [BillPay].BillPayID, [Payee].PayeeName, [BillPay].Amount, [BillPay].ScheduleDate, [BillPay].Period, [BillPay].ModifyDate" +
                " FROM [BillPay] FULL OUTER JOIN [Payee] ON [BillPay].PayeeID = [Payee].PayeeID WHERE AccountNumber=" + accountID, con);
            SqlDataReader sqlr = command.ExecuteReader();
            while (sqlr.Read())
            {
                _Bills.Add(new BillDisplay(sqlr.GetInt32(0), sqlr.GetString(1),
                    sqlr.GetDecimal(2), sqlr.GetDateTime(3),
                    sqlr.GetString(4).ToCharArray()[0], sqlr.GetDateTime(5)));
            }
            con.Close();
            return _Bills;
        }

        public static Boolean ServiceFee(Account acct)
        {
            //if apply create a service fee Bill
            Transaction newTrans = new Transaction('S', acct.AccountNumber, acct.AccountNumber,
                decimal.Parse("0.3"), "BPay Service Fee", DateTime.Now);
            if (TransactionRepository.Insert(newTrans))
            {
                if (AccountRepostiory.UpdateWithdraw(acct, decimal.Parse("0.3")))
                {
                    //service fee apply and has succsfully created a service fee trnsaction for it.
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                //any thing wrong during the proceess just return false
                return false;
            }
        }

        public static Boolean Insert(Bill bill)
        {
            //insert a new Bill into database
            SqlCommand command = new SqlCommand("INSERT INTO [BillPay] ([AccountNumber],[PayeeID],[Amount],[ScheduleDate],[Period],[ModifyDate]) " +
                "values (@Num, @PayeeID, @Amt, @SchDate, @Period, @date)");
            //specification for each of the insert value
            command.Parameters.AddWithValue("@ID", SqlDbType.Int);

            command.Parameters.AddWithValue("@Num", SqlDbType.Int).Value = bill.AccountNumber;

            command.Parameters.AddWithValue("@PayeeID", SqlDbType.Int).Value = bill.PayeeID;

            command.Parameters.AddWithValue("@Amt", SqlDbType.Money).Value = bill.Amount;

            command.Parameters.AddWithValue("@SchDate", SqlDbType.DateTime).Value = bill.ScheduleDate;

            command.Parameters.AddWithValue("@Period", SqlDbType.NVarChar).Value = bill.Period;

            command.Parameters.AddWithValue("@date", SqlDbType.DateTime).Value = bill.ModifyDate;
            con.Open();
            command.Connection = con;

            try
            {
                command.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static Boolean Update(int id, Bill bill)
        {
            try
            {
                con.Open();
                SqlCommand command = new SqlCommand("UPDATE [BillPay] " +
                    "SET AccountNumber=@acctNum, PayeeID=@payeeID, Amount=@amt, "+
                    "ScheduleDate=@scheDate, Period=@period, ModifyDate=@date " +
                    "WHERE BillPayID=" + id, con);
                command.Parameters.AddWithValue("@acctNum", bill.AccountNumber);
                command.Parameters.AddWithValue("@payeeID", bill.PayeeID);
                command.Parameters.AddWithValue("@amt", bill.Amount);
                command.Parameters.AddWithValue("@scheDate", bill.ScheduleDate);
                command.Parameters.AddWithValue("@period", bill.Period);
                command.Parameters.AddWithValue("@date", DateTime.Now);
                command.ExecuteNonQuery();
                return true;
            }
            catch (Exception o)
            {
                return false;
            }
            finally
            {
                con.Close();
            }
        }

        public static Boolean DeleteBill(int id)
        {
            try
            {
                con.Open();
                SqlCommand command = new SqlCommand("DELETE FROM [BillPay] " +
                    "WHERE BillPayID=" + id, con);
                command.ExecuteNonQuery();
                return true;
            }
            catch (Exception o)
            {
                return false;
            }
            finally
            {
                con.Close();
            }
        }

        public static string TriggerBills(Account acct, DateTime now)
        {
            string msg = "";
            List<Bill> bills = Select(acct.AccountNumber);
            foreach (Bill bill in bills)
            {
                if (bill.ScheduleDate <= now)
                {
                    Transaction newTrans = new Transaction('W', acct.AccountNumber, acct.AccountNumber,
                        decimal.Parse(bill.Amount.ToString()), "Billed by " +
                        PayeeRepository.SelectNameByID(bill.PayeeID), bill.ScheduleDate);
                    if (TransactionRepository.Insert(newTrans))
                    {
                        if (!AccountRepostiory.UpdateWithdraw(acct, Decimal.Parse(bill.Amount.ToString())))
                        {
                            msg += "Failed to pay Bill #"+bill.ID+";\n";
                            return msg;
                        }
                    }
                    else
                    {
                        msg += "Failed to create transaction record for Bill #"+bill.ID+";\n";
                        return msg;
                    }
                    DeleteBill(bill.ID);
                    var newBill = bill;
                    newBill.ModifyDate = DateTime.Now;
                    switch (bill.Period)
                    {
                        case 'O':
                            break;
                        case 'D':
                            newBill.ScheduleDate = newBill.ScheduleDate.AddDays(1);
                            Insert(newBill);
                            break;
                        case 'M':
                            newBill.ScheduleDate = newBill.ScheduleDate.AddMonths(1);
                            Insert(newBill);
                            break;
                        case 'A':
                            newBill.ScheduleDate = newBill.ScheduleDate.AddYears(1);
                            Insert(newBill);
                            break;
                    }
                    msg += "Bill #" + bill.ID + " paid;\n";
                }
            }
            return msg;
        }
    }
}